package com.oa.ylms.apps.dao.notice.impl;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.oa.ylms.apps.constants.IDEDStates;
import com.oa.ylms.apps.dao.notice.INoticeInfoDao;
import com.oa.ylms.apps.entity.notice.NoticeInfoValue;

@SuppressWarnings("unchecked")
public class NoticeInfoDaoImpl extends HibernateDaoSupport implements
		INoticeInfoDao {

	private List<NoticeInfoValue> list;
	private int numbers;

	public boolean deleteNoticeInfo(NoticeInfoValue noticeInfo) throws Exception {
		if (noticeInfo != null) {
			try {
				this.getHibernateTemplate().delete(noticeInfo);
			} catch (Exception e) {
				return false;
			}
			return true;
		}
		return false;
	}

	public int addNoticeInfo(NoticeInfoValue noticeInfo) throws Exception {
		if (noticeInfo != null) {
			this.getHibernateTemplate().save(noticeInfo);
			return noticeInfo.getNoticeId();
		}
		return 0;
	}

	public NoticeInfoValue queryNoticeInfoByNoticeId(int noticeId) {

		return (NoticeInfoValue) this.getHibernateTemplate().get(
				NoticeInfoValue.class, noticeId);
	}

	public int countTpages(final NoticeInfoValue noticeInfo, final int rows)
			throws Exception {
		int pageNumbers = 0;
		String hqlStr = "select niv from NoticeInfoValue niv where niv.states = ? ";
		if (noticeInfo.getPlace() != null) {
			hqlStr += " AND niv.place =?  ";
		}
		if (noticeInfo.getTitle() != null) {
			hqlStr += " And niv.title like ? ";
		}
		final String hql = hqlStr;
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				int index = 0;
				Query query = session.createQuery(hql);
				query.setInteger(index, IDEDStates.ENABLE);
				if (noticeInfo.getPlace() != null) {
					index++;
					query.setString(index, noticeInfo.getPlace());
				}
				if (noticeInfo.getTitle() != null) {
					index++;
					query.setString(index, noticeInfo.getTitle());
				}
				list = query.list();
				return list;
			}
		});
		this.numbers = list.size();
		if (numbers <= rows) {
			pageNumbers = 1;
		} else {
			if (numbers % rows == 0) {
				pageNumbers = numbers / rows;
			} else {
				pageNumbers = numbers / rows + 1;
			}
		}
		return pageNumbers;
	}

	public List<NoticeInfoValue> queryNoticeInfoWithPage(
			final NoticeInfoValue noticeInfo, final int rows, final int pageNo)
			throws Exception {
		String hqlStr = "select niv from NoticeInfoValue niv where niv.states = ?";
		if (noticeInfo.getPlace() != null) {
			hqlStr += " And niv.place= ?";
		}
		if (noticeInfo.getTitle() != null) {
			hqlStr += " And niv.title like ?";
		}
		hqlStr += " order BY niv.publishTime DESC";
		final String hql = hqlStr;
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				int index = 0;
				Query query = session.createQuery(hql);
				query.setInteger(index, IDEDStates.ENABLE);
				if (noticeInfo.getPlace() != null) {
					index++;
					query.setString(index, noticeInfo.getPlace());
				}
				if (noticeInfo.getTitle() != null) {
					index++;
					query.setString(index, noticeInfo.getTitle());
				}
				query.setFirstResult(rows * (pageNo - 1));
				query.setMaxResults(rows);
				list = query.list();
				return list;
			}
		});
		return list;
	}

	public List<NoticeInfoValue> queryTipWithPage(final int sectionId,
			final int staffId, final NoticeInfoValue noticeInfo,
			final int rows, final int pageNo) throws Exception {
		String hqlStr = "select niv from NoticeInfoValue niv, NoticeSectionValue nsev, NoticeStaffValue nstv where niv.noticeId = nsev.noticeInfo.noticeId and nsev.sectionInfo.sectionId = ? and UNIX_TIMESTAMP(niv.endTime) > UNIX_TIMESTAMP(now()) or niv.noticeId = nstv.noticeInfo.noticeId  and nstv.staffInfo.staffId = ? and UNIX_TIMESTAMP(niv.endTime) > UNIX_TIMESTAMP(now()) and niv.states = ? ";
		if (noticeInfo.getPlace() != null) {
			hqlStr += " And niv.place= ?";
		}
		if (noticeInfo.getTitle() != null) {
			hqlStr += " And niv.title like ?";
		}
		hqlStr += " group by niv.noticeId";
		hqlStr += " order by niv.publishTime DESC";
		final String hql = hqlStr;
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				query.setInteger(0, sectionId);
				query.setInteger(1, staffId);
				query.setInteger(2, IDEDStates.ENABLE);
				int index = 2;
				if (noticeInfo.getPlace() != null) {
					index++;
					query.setString(index, noticeInfo.getPlace());
				}
				if (noticeInfo.getTitle() != null) {
					index++;
					query.setString(index, noticeInfo.getTitle());
				}
				query.setFirstResult(rows * (pageNo - 1));
				query.setMaxResults(rows);
				list = query.list();
				return list;
			}
		});
		return list;
	}

	public int countTpagesByTip(final int sectionId, final int staffId,
			final NoticeInfoValue noticeInfo, final int rows) throws Exception {
		int pageNumbers = 0;
		String hqlStr = "select niv from NoticeInfoValue niv, NoticeSectionValue nsev, NoticeStaffValue nstv where niv.noticeId = nsev.noticeInfo.noticeId and nsev.sectionInfo.sectionId = ? and UNIX_TIMESTAMP(niv.endTime) > UNIX_TIMESTAMP(now()) or niv.noticeId = nstv.noticeInfo.noticeId  and nstv.staffInfo.staffId = ? and UNIX_TIMESTAMP(niv.endTime) > UNIX_TIMESTAMP(now()) and niv.states = ? ";
		if (noticeInfo.getPlace() != null) {
			hqlStr += " And niv.place= ?";
		}
		if (noticeInfo.getTitle() != null) {
			hqlStr += " And niv.title like ?";
		}
		hqlStr += " group by niv.noticeId";
		hqlStr += " order by niv.publishTime DESC";
		final String hql = hqlStr;
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				query.setInteger(0, sectionId);
				query.setInteger(1, staffId);
				query.setInteger(2, IDEDStates.ENABLE);
				int index = 2;
				if (noticeInfo.getPlace() != null) {
					index++;
					query.setString(index, noticeInfo.getPlace());
				}
				if (noticeInfo.getTitle() != null) {
					index++;
					query.setString(index, noticeInfo.getTitle());
				}
				list = query.list();
				return list;
			}
		});
		this.numbers = list.size();
		if (numbers <= rows) {
			pageNumbers = 1;
		} else {
			if (numbers % rows == 0) {
				pageNumbers = numbers / rows;
			} else {
				pageNumbers = numbers / rows + 1;
			}
		}
		return pageNumbers;
	}

	public int countTpagesByStaffId(final int staffId,
			final NoticeInfoValue noticeInfo, final int rows) throws Exception {
		int pageNumbers = 0;
		String hqlStr = "select niv from NoticeInfoValue niv where niv.staffInfo.staffId = ? and UNIX_TIMESTAMP(niv.startTime) > UNIX_TIMESTAMP(now()) and niv.states = ?";
		if (noticeInfo.getPlace() != null) {
			hqlStr += " And niv.place= ?";
		}
		if (noticeInfo.getTitle() != null) {
			hqlStr += " And niv.title like ?";
		}
		final String hql = hqlStr;
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				query.setInteger(0, staffId);
				query.setInteger(1, IDEDStates.ENABLE);
				int index = 1;
				if (noticeInfo.getPlace() != null) {
					index++;
					query.setString(index, noticeInfo.getPlace());
				}
				if (noticeInfo.getTitle() != null) {
					index++;
					query.setString(index, noticeInfo.getTitle());
				}
				list = query.list();
				return list;
			}
		});
		this.numbers = list.size();
		if (numbers <= rows) {
			pageNumbers = 1;
		}
		if (numbers == 0) {
			pageNumbers = 0;
		} else {
			if (numbers % rows == 0) {
				pageNumbers = numbers / rows;
			} else {
				pageNumbers = numbers / rows + 1;
			}
		}
		return pageNumbers;
	}

	public List<NoticeInfoValue> queryNoticeInfoByStaffIdWithPage(
			final int staffId, final NoticeInfoValue noticeInfo,
			final int rows, final int pageNo) throws Exception {
		String hqlStr = "select niv from NoticeInfoValue niv where niv.staffInfo.staffId = ? and UNIX_TIMESTAMP(niv.startTime) > UNIX_TIMESTAMP(now()) and niv.states = ?";
		if (noticeInfo.getPlace() != null) {
			hqlStr += " And niv.place= ?";
		}
		if (noticeInfo.getTitle() != null) {
			hqlStr += " And niv.title like ?";
		}
		hqlStr += " order BY niv.publishTime DESC";
		final String hql = hqlStr;
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				query.setInteger(0, staffId);
				query.setInteger(1, IDEDStates.ENABLE);
				int index = 1;
				if (noticeInfo.getPlace() != null) {
					index++;
					query.setString(index, noticeInfo.getPlace());
				}
				if (noticeInfo.getTitle() != null) {
					index++;
					query.setString(index, noticeInfo.getTitle());
				}
				query.setFirstResult(rows * (pageNo - 1));
				query.setMaxResults(rows);
				list = query.list();
				return list;
			}
		});
		return list;
	}

	public int getTotalNumber() throws Exception {
		return numbers;
	}

	public List<NoticeInfoValue> getList() {
		return list;
	}

	public void setList(List<NoticeInfoValue> list) {
		this.list = list;
	}

	public int getNumbers() {
		return numbers;
	}

	public void setNumbers(int numbers) {
		this.numbers = numbers;
	}

	public List<NoticeInfoValue> queryTip() throws Exception {
		String hqlStr = "select niv from NoticeInfoValue niv where UNIX_TIMESTAMP(niv.endTime) > UNIX_TIMESTAMP(now())";
		hqlStr += " group by niv.noticeId";
		hqlStr += " order by niv.publishTime DESC";
		final String hql = hqlStr;
		list = this.getHibernateTemplate().executeFind(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query = session.createQuery(hql);
				list = query.list();
				return list;
			}
		});
		return list;
	}

}
